Amazon Athenaで週次の集計を行う

データアナリティクス事業本部の鈴木です。

今回は、Amazon Athenaで週次の集計を行う例を紹介します。

やりたいこと

時系列データを分析・利用する際に、ある程度まとまった期間で集計したいことがあります。

月次の場合は日付にある月の値を取得してGROUP BYのグループキーに指定すれば良いですが、週次で集計を行う際には、日付から週を表す値を作る必要があります。

この方法として、Athena エンジンバージョン2が基づいているPresto 0.217のドキュメントのExtraction Functionのセクションに、week関数を使った方法が記載されているので試してみました。

6.13. Date and Time Functions and Operators — Presto 0.217 Documentation

week関数は、Date型を渡すことで、その日付のISO週番号を取得できます。

つまり、今回の週次とはISO週番号で定義された週を意味することにご注意ください。

やってみる

以下のようなデータがあるとして、週次ごとにvalueの値を合計してみます。

timestamp, value
2021-05-26 01:00:00,100
2021-05-27 01:00:00,110
2021-06-03 01:00:00,115
2021-06-06 01:00:00,105
2021-06-16 01:00:00,95
2021-06-30 01:00:00,100
2021-07-22 01:00:00,105

まず、このデータをS3バケットにアップロードしておきます。

データはヘッダー付きのCSVファイルなので、以下のようにテーブルを作成します。

CREATE EXTERNAL TABLE `データベース名`.`テーブル名`(
  `timestamp` STRING, 
  `value` INT)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'ファイルのある階層のS3　URL'
TBLPROPERTIES (
  'skip.header.line.count'='1')

上記SQLをAthenaで実行し、テーブルができたところで、以下のSQLでデータを週次集計してみます。

with data_with_week_number AS ( 
  SELECT 
      week(date_parse(timestamp, '%Y-%m-%d %H:%i:%s')) AS week_number,
      value
  FROM "データベース名"."テーブル名"
)
SELECT 
    sum(value) AS total,
    week_number
FROM data_with_week_number
GROUP BY week_number
ORDER BY week_number

ポイントは以下になります。

• timestampカラムをdate_parse関数でDate型にする。
• Date型に変換したtimestampカラムの値から、week関数でISO週番号を取得し、week_numberカラムとする。
• week_numberカラムをグループキーにGROUP BYする。

結果は以下のようになり、週番号ごとに値を合計することができました。

最後に

AthenaでISO週番号を使った週次集計の例を紹介しました。

ご存知の方だと当たり前の方法かもしれませんが、調べたところ、意外と情報がなかったので記事にしてみました。

どこかで誰かのお役に立てば幸いです。